package tt.dz.biz.dao;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.session.Session;
import org.apache.shiro.subject.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.JdbcUtils;
import tt.dz.common.CamelCaseUtil;
import tt.dz.vo.BaseVO;

public class BaseDAO
{
  private static final String COMMON_BASE_QUERY_SEQ = "select nextval('common_base_seq');";
  private static final String USERKEY = "dzSessionAttrUserId";
  @Autowired
  protected NamedParameterJdbcTemplate jdbcTemplate;
  
  public NamedParameterJdbcTemplate getJdbcTemplate()
  {
    return this.jdbcTemplate;
  }
  
  public void setJdbcTemplate(NamedParameterJdbcTemplate jdbcTemplate)
  {
    this.jdbcTemplate = jdbcTemplate;
  }
  
  protected int save(String sql, Object obj)
  {
    if (BaseVO.class.isAssignableFrom(obj.getClass()))
    {
      BaseVO baseVO = (BaseVO)obj;
      
      String managerId = getSystemCurrentOper();
      Date now = new Date();
      if (baseVO.getCreatedBy() == null)
      {
        baseVO.setCreatedBy(managerId);
        baseVO.setCreatedTime(now);
        baseVO.setUpdatedTime(now);
        baseVO.setUpdatedBy(managerId);
      }
      else
      {
        baseVO.setUpdatedBy(managerId);
        baseVO.setUpdatedTime(now);
      }
    }
    SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(obj);
    return this.jdbcTemplate.update(sql, sqlParameterSource);
  }
  
  protected int save(String sql, MapSqlParameterSource parameters)
  {
    return this.jdbcTemplate.update(sql, parameters);
  }
  
  public synchronized Long querySequence()
  {
    SqlParameterSource param = null;
    return (Long)getJdbcTemplate().queryForObject("select nextval('common_base_seq');", param, new SingleColumnRowMapper());
  }
  
  public Long querySequence(String seqName)
  {
    SqlParameterSource param = null;
    return (Long)getJdbcTemplate().queryForObject("select nextval('" + seqName + "')", param, new SingleColumnRowMapper());
  }
  
  protected List<Map<String, Object>> queryForList(String sql, Object param)
  {
    SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(param);
    return getJdbcTemplate().queryForList(sql, sqlParameterSource);
  }
  
  protected int[] batchSave(String sql, List<?> paramList)
  {
    if (paramList.isEmpty()) {
      return new int[0];
    }
    SqlParameterSource[] sqlParam = new SqlParameterSource[paramList.size()];
    for (int i = 0; i < paramList.size(); i++) {
      sqlParam[i] = new BeanPropertySqlParameterSource(paramList.get(i));
    }
    return this.jdbcTemplate.batchUpdate(sql, sqlParam);
  }
  
  protected <T> T queryForObject(String sql, Object param, Class<T> c)
  {
    SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(param);
    if (c == String.class) {
      return (T) this.jdbcTemplate.queryForObject(sql, sqlParameterSource, String.class);
    }
    RowMapper rw = new BeanPropertyRowMapper(c);
    return (T) this.jdbcTemplate.queryForObject(sql, sqlParameterSource, rw);
  }
  
  protected int queryForInt(String sql, Object param)
  {
    SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(param);
    Number number = (Number)this.jdbcTemplate.queryForObject(sql, sqlParameterSource, Integer.class);
    return number != null ? number.intValue() : 0;
  }
  
  protected List queryForList(String sql, Object param, Class c)
  {
    SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(param);
    RowMapper rw = new BeanPropertyRowMapper(c);
    return this.jdbcTemplate.query(sql, sqlParameterSource, rw);
  }
  
  protected List queryForList(String sql, MapSqlParameterSource parameters, Class c)
  {
    RowMapper rw = new BeanPropertyRowMapper(c);
    return this.jdbcTemplate.query(sql, parameters, rw);
  }
  
  protected <T> List<T> querySingleColumnForList(String sql, SqlParameterSource sqlParameters, Class<T> requiredType)
  {
    RowMapper<T> rowMapper = new SingleColumnRowMapper();
    return this.jdbcTemplate.query(sql, sqlParameters, rowMapper);
  }
  
  protected List<Map<String, Object>> queryForList(String sql, MapSqlParameterSource parameters)
  {
    return this.jdbcTemplate.queryForList(sql, parameters);
  }
  
  private class ColumnMapCamelRowMapper
    extends ColumnMapRowMapper
  {
    private ColumnMapCamelRowMapper() {}
    
    public Map<String, Object> mapRow(ResultSet rs, int rowNum)
      throws SQLException
    {
      ResultSetMetaData rsmd = rs.getMetaData();
      int columnCount = rsmd.getColumnCount();
      Map<String, Object> mapOfColValues = createColumnMap(columnCount);
      for (int i = 1; i <= columnCount; i++)
      {
        String key = getColumnKey(CamelCaseUtil.ToCamelCase(JdbcUtils.lookupColumnName(rsmd, i)));
        Object obj = getColumnValue(rs, i);
        mapOfColValues.put(key, obj);
      }
      return mapOfColValues;
    }
  }
  
  public String getSystemCurrentOper()
  {
    try
    {
      Subject subject = SecurityUtils.getSubject();
      return (String)subject.getSession().getAttribute("dzSessionAttrUserId");
    }
    catch (Exception e) {}
    return null;
  }
  
  public String assemblyConditions(Object obj, String alias, boolean isLike)
  {
    StringBuilder conditionSql = new StringBuilder("");
    Field[] fields = obj.getClass().getDeclaredFields();
    for (Field field : fields)
    {
      String fieldName = field.getName();
      if (!fieldName.equals("serialVersionUID")) {
        try
        {
          PropertyDescriptor p = new PropertyDescriptor(fieldName, obj.getClass());
          Method getMethod = p.getReadMethod();
          Object value = getMethod.invoke(obj, new Object[0]);
          if ((value != null) && (!StringUtils.isBlank(value.toString()))) {
            conditionSql.append(" and " + alias + "." + assemblyColumnName(fieldName) + " " + ((isLike) && (!(value instanceof Integer)) ? "like" : "=") + " :" + fieldName + " ");
          }
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }
    }
    return conditionSql.toString();
  }
  
  public Object convertObjectForLike(Object obj)
  {
    Field[] fields = obj.getClass().getDeclaredFields();
    for (Field field : fields)
    {
      String fieldName = field.getName();
      if (!fieldName.equals("serialVersionUID")) {
        try
        {
          PropertyDescriptor p = new PropertyDescriptor(fieldName, obj.getClass());
          Method getMethod = p.getReadMethod();
          Object value = getMethod.invoke(obj, new Object[0]);
          if ((value != null) && (!StringUtils.isBlank(value.toString())) && (!(value instanceof Integer)))
          {
            String destValue = "%" + value.toString() + "%";
            p.getWriteMethod().invoke(obj, new Object[] { destValue });
          }
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
      }
    }
    return obj;
  }
  
  public String assemblyColumnName(String fieldName)
  {
    List<String> list = new ArrayList();
    char c;
    for (int i = 0; i < fieldName.length(); i++)
    {
      c = fieldName.charAt(i);
      if (Character.isUpperCase(c)) {
        list.add("_" + Character.toLowerCase(c));
      } else {
        list.add(String.valueOf(c));
      }
    }
    StringBuilder builder = new StringBuilder("");
    for (String s : list) {
      builder.append(s);
    }
    return builder.toString();
  }
}
